from domain.models.models import Activity
from domain.models.models import Address
from domain.models.models import ActivityLog
from domain.models.models import UserFav  
from domain.models.models import UserReg 
from sqlalchemy import select, and_, or_
from sqlalchemy import func
from sqlalchemy import text
from math import cos, sin, radians, sqrt, asin
from db.db import async_session
import json

async def create_new_activity(user_id: int, activity_data: dict) -> tuple[bool, str]:
    """创建新活动"""
    async with async_session() as session:
        try:
            # 1. 创建地址
            new_address = Address(
                address=activity_data['address'],
                name=activity_data['address_name'],
                longitude=activity_data['longitude'],
                latitude=activity_data['latitude']
            )
            session.add(new_address)
            await session.flush()  # 获取地址ID
            print("create address",user_id ,activity_data)
            # 2. 创建活动
            new_activity = Activity(
                images=json.dumps(activity_data['images']),
                name=activity_data['name'],
                content=activity_data['content'],
                type=activity_data['type'],
                address_id=new_address.id,
                people_number=activity_data['people_number'],
                people_age=activity_data.get('people_age', ''),
                fee=activity_data['fee'],
                start_time=activity_data['start_time'],
                end_time=activity_data['end_time'],
                created_user_id=user_id
            )
            session.add(new_activity)
            await session.flush()  # 获取活动ID
            print("create activity")

            
            # 3. 记录操作日志
            activity_log = ActivityLog(
                activity_id=new_activity.id,
                operator_id=user_id,
                operation_type=0,  # 0-创建
                operation_content="创建新活动",
                new_value=json.dumps(activity_data)
            )
            session.add(activity_log)
            
            await session.commit()
            return True, new_activity.id
            
        except Exception as e:
            await session.rollback()
            return False, str(e)

async def update_activity_info(activity_id: int, user_id: int, activity_data: dict) -> tuple[bool, str]:
    """修改活动信息"""
    async with async_session() as session:
        try:
            # 1. 获取原活动信息
            activity = await session.get(Activity, activity_id)
            if not activity or activity.is_delete == 1:
                return False, "活动不存在"
            
            if activity.created_user_id != user_id:
                return False, "无权修改此活动"
            
            # 获取并更新地址信息
            if 'address' in activity_data:
                address = await session.get(Address, activity.address_id)
                if address:
                    # 更新地址信息
                    address.address = activity_data.get('address')
                    address.name = activity_data.get('address_name')
                    address.longitude = activity_data.get('longitude')
                    address.latitude = activity_data.get('latitude')
            
            old_value = {
                "name": activity.name,
                "content": activity.content,
                "type": activity.type,
                "people_number": activity.people_number,
                "people_age": activity.people_age,
                "fee": activity.fee,
                "start_time": activity.start_time,
                "end_time": activity.end_time,
                "images": json.loads(activity.images),
                "address": {
                    "address": address.address,
                    "name": address.name,
                    "longitude": address.longitude,
                    "latitude": address.latitude
                } if 'address' in activity_data else None
            }
            
            # 2. 更新活动信息
            for key, value in activity_data.items():
                if key not in ['address', 'address_name', 'longitude', 'latitude']:
                    if key == 'images':
                        setattr(activity, key, json.dumps(value))
                    else:
                        setattr(activity, key, value)
            
            # 3. 记录操作日志
            activity_log = ActivityLog(
                activity_id=activity_id,
                operator_id=user_id,
                operation_type=1,  # 1-修改
                operation_content="修改活动信息",
                old_value=json.dumps(old_value),
                new_value=json.dumps(activity_data)
            )
            session.add(activity_log)
            
            await session.commit()
            return True, "修改成功"
            
        except Exception as e:
            await session.rollback()
            return False, str(e)


async def get_activity_info(activity_id: int, current_user: int) -> tuple[bool, dict]:
    """获取单个活动详情"""
    async with async_session() as session:
        try:
            # 联合查询活动和地址信息
            stmt = select(Activity, Address).join(
                Address, Activity.address_id == Address.id
            ).where(
                Activity.id == activity_id,
                Activity.is_delete == 0
            )
            result = await session.execute(stmt)
            activity, address = result.first()
            
            if not activity:
                return False, "活动不存在"
             # 查询当前用户是否收藏该活动
            fav_stmt = select(UserFav).where(
                and_(
                    UserFav.user_id == current_user,
                    UserFav.activity_id == activity_id,
                    UserFav.is_delete == 0
                )
            )
            fav_result = await session.execute(fav_stmt)
            is_favorited = bool(fav_result.scalar_one_or_none())


            # 查询当前用户是否已报名该活动
            reg_stmt = select(UserReg).where(
                and_(
                    UserReg.user_id == current_user,
                    UserReg.activity_id == activity_id,
                    UserReg.is_delete == 0
                )
            )
            reg_result = await session.execute(reg_stmt)
            reg_record = reg_result.scalar_one_or_none()
            
            is_registered = reg_record.status if reg_record else -1


            return True, {
                "id": activity.id,
                "name": activity.name,
                "content": activity.content,
                "type": activity.type,
                "images": json.loads(activity.images),
                "address": {
                    "id": address.id,
                    "name": address.name,
                    "address": address.address,
                    "longitude": address.longitude,
                    "latitude": address.latitude
                },
                "people_number": activity.people_number,
                "people_age": activity.people_age,
                "fee": activity.fee,
                "start_time": activity.start_time,
                "end_time": activity.end_time,
                "status": activity.status,
                "created_user_id": activity.created_user_id,
                "joiners_ids": activity.joiners_ids.split(',') if activity.joiners_ids else [],
                "is_favorited": is_favorited,
                "is_registered": is_registered
            }
        except Exception as e:
            return False, str(e)

async def get_activity_members(activity_id: int) -> tuple[bool, list]:
    """获取活动成员列表"""
    async with async_session() as session:
        try:
            # 获取活动信息
            activity = await session.get(Activity, activity_id)
            if not activity or activity.is_delete == 1:
                return False, "活动不存在"
            
            # 获取报名成员ID列表
            member_ids = activity.joiners_ids.split(',') if activity.joiners_ids else []
            if not member_ids:
                return True, []
            
            # 查询成员信息
            stmt = select(Girls).where(
                Girls.id.in_(member_ids),
                Girls.is_delete == 0
            )
            result = await session.execute(stmt)
            members = []
            
            for member in result.scalars():
                members.append({
                    "user_id": str(member.id),
                    "nickname": member.nickname,
                    "avatar_url": member.avatar_url
                })
            
            return True, members
            
        except Exception as e:
            return False, str(e)


async def get_nearby_activities(latitude: float, longitude: float, distance: float = 10,
                              activity_type: str = None, status: list = None,
                              page: int = 1, page_size: int = 20) -> tuple[bool, dict]:
    try:
        async with async_session() as session:
            # 先获取一个粗略的范围
            lat_range = distance / 111.0  # 1度纬度约等于111公里
            lon_range = distance / (111.0 * cos(radians(latitude)))  # 经度范围随纬度变化
            
            # 基础查询条件
            conditions = [
                Activity.is_delete == 0,
                Address.latitude.between(latitude - lat_range, latitude + lat_range),
                Address.longitude.between(longitude - lon_range, longitude + lon_range)
            ]
            
            if activity_type:
                conditions.append(Activity.type == activity_type)
            if status:
                conditions.append(Activity.status.in_(status))

            # 查询活动和地址信息
            stmt = select(Activity, Address).join(
                Address, Activity.address_id == Address.id
            ).where(and_(*conditions))
            
            result = await session.execute(stmt)
            all_activities = []
            
            # 在Python中计算实际距离并筛选
            R = 6371.0  # 地球半径（公里）
            for activity, address in result:
                lat1, lon1 = radians(latitude), radians(longitude)
                lat2, lon2 = radians(address.latitude), radians(address.longitude)
                
                dlat = lat2 - lat1
                dlon = lon2 - lon1
                
                a = sin(dlat/2)**2 + cos(lat1) * cos(lat2) * sin(dlon/2)**2
                c = 2 * asin(sqrt(a))
                actual_distance = R * c
                
                if actual_distance <= distance:
                    all_activities.append({
                        "id": activity.id,
                        "name": activity.name,
                        "type": activity.type,
                        "status": activity.status,
                        "address": {
                            "name": address.name,
                            "address": address.address,
                            "longitude": address.longitude,
                            "latitude": address.latitude
                        },
                        "distance": round(actual_distance, 2),
                        "images": json.loads(activity.images),
                        "fee": activity.fee,
                        "start_time": activity.start_time,
                        "end_time": activity.end_time,
                        "people_number": activity.people_number
                    })
            
            # 按距离排序
            all_activities.sort(key=lambda x: x["distance"])
            
            # 分页处理
            total = len(all_activities)
            start = (page - 1) * page_size
            end = start + page_size
            
            return True, {
                "total": total,
                "page": page,
                "page_size": page_size,
                "activities": all_activities[start:end]
            }
            
    except Exception as e:
        return False, str(e)

async def get_activity_type_fav_stats(city: str = None) -> tuple[bool, dict]:
    """获取活动类型收藏统计
    Args:
        city: 城市名称，可选
    Returns:
        (success, result)
        success: bool, 是否成功
        result: dict, 统计结果，格式如下：
        {
            "深圳": {
                "拍照": 10,
                "运动": 5,
                "美食": 8,
                ...
            },
            "广州": {
                "拍照": 6,
                "运动": 3,
                "美食": 4,
                ...
            }
        }
    """
    async with async_session() as session:
        try:
            # 构建基础查询
            query = select(
                Activity.type,
                func.count(UserFav.id).label('fav_count')
            ).join(
                UserFav,
                and_(
                    Activity.id == UserFav.activity_id,
                    UserFav.is_delete == 0
                )
            ).join(
                Address,
                and_(
                Address.name.like(f'%{city}%'),
                Activity.address_id == Address.id
                )
            ).where(
                Activity.is_delete == 0
            ).group_by(
                Activity.type
            )
            # 如果指定了城市，添加城市过滤条件
            # if city:
            #     query = query.where(Address.name.like(f'%{city}%'))            
            # 执行查询
            result = await session.execute(query)
            rows = result.fetchall()
            print(rows)
           
            # 整理数据格式
            stats = {}
            for row in rows:
                activity_type = row.type
                fav_count = row.fav_count
                stats[activity_type] = fav_count
            return True, stats
            
        except Exception as e:
            print(str(e))
            return False, str(e)

async def get_activity_list(
    user_id: int = None,
    activity_type: str = None,
    status: list = None,
    page: int = 1,
    page_size: int = 20,
    start_date: str = None,
    end_date: str = None,
    city: str = None,
    keyword: str = None,
    area: str = None,  # 添加地区筛选参数
    people: str = None  # 添加人数筛选参数
) -> tuple[bool, dict]:
    """获取活动列表"""
    print(status, page, page_size, start_date, end_date, activity_type, user_id, city, keyword, area, people)
    async with async_session() as session:
        try:
            # 构建查询条件
            conditions = [Activity.is_delete == 0]
            if user_id:
                conditions.append(Activity.created_user_id == user_id)
            if activity_type:
                conditions.append(Activity.type == activity_type)
            if status:
                conditions.append(Activity.status.in_(status))
            if start_date:
                conditions.append(Activity.start_time >= start_date)
            if end_date:
                conditions.append(Activity.start_time <= end_date)
            if city:
                conditions.append(Address.name.like(f'%{city}%'))
            if keyword:
                conditions.append(or_(
                    Activity.name.like(f'%{keyword}%'),
                    Activity.content.like(f'%{keyword}%')
                ))
            
            # 添加地区筛选条件
            if area:
                conditions.append(Address.name.like(f'%{area}%'))
            
            # 添加人数筛选条件
            if people:
                if people == '2人':
                    conditions.append(Activity.people_number == 2)
                elif people == '3-5人':
                    conditions.append(and_(Activity.people_number >= 3, Activity.people_number <= 5))
                elif people == '6-10人':
                    conditions.append(and_(Activity.people_number >= 6, Activity.people_number <= 10))
                elif people == '10人以上':
                    conditions.append(Activity.people_number > 10)
            
            # 查询总数
            count_stmt = select(Activity).join(
                Address, Activity.address_id == Address.id
            ).where(and_(*conditions))
            total = len((await session.execute(count_stmt)).all())
            
            # 分页查询
            stmt = select(Activity, Address).join(
                Address, Activity.address_id == Address.id
            ).where(
                and_(*conditions)
            ).order_by(
                Activity.c_time.desc()
            ).offset(
                (page - 1) * page_size
            ).limit(page_size)
            
            result = await session.execute(stmt)
            activities = []
            for activity, address in result:
                activities.append({
                    "id": activity.id,
                    "name": activity.name,
                    "content": activity.content,
                    "type": activity.type,
                    "images": json.loads(activity.images),
                    "fee": activity.fee,
                    "address": {
                        "name": address.name,
                        "address": address.address,
                        "longitude": address.longitude,
                        "latitude": address.latitude
                    },
                    "start_time": activity.start_time,
                    "end_time": activity.end_time,
                    "status": activity.status,
                    "people_number": activity.people_number,
                    "created_user_id": activity.created_user_id
                })
            
            return True, {
                "total": total,
                "page": page,
                "page_size": page_size,
                "activities": activities
            }
            
        except Exception as e:
            return False, str(e)